package com.superhelper.common.mapper.stat;

import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.superhelper.common.domain.stat.StatDailyUser;

public interface StatDailyUserMapper {

    @Select("select a.shopId,a.type as shopType, count(distinct b.id) as cnt,"
            + "    count(DISTINCT if(b.createAt >= #{startDate} and b.createAt < #{endDate}, b.id ,null)) as newCnt,"
            + "    count(DISTINCT if(b.createAt < #{startDate}, b.id,null)) as oldCnt "
            + "    from takeout_order a, `user` b where a.userId = b.id AND "
            + "    a.createAt >= #{startDate} and a.createAt < #{endDate} group by a.shopId,a.`type`")
    List<StatDailyUser> statDailyUser(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    int batchAdd(List<StatDailyUser> stats);

    @Select("select shopId,`type` as shopType ,count(DISTINCT userId) as actCnt from takeout_order where "
            + "    createAt >= #{startDate} and `status`='settled' group by shopId,`type`")
    List<StatDailyUser> statActCnt(@Param("startDate") Date startDate);

    @Select("select shopId,`type` as shopType ,count(DISTINCT userId) as slientCnt from takeout_order where "
            + "    createAt >= #{startDate} and createAt < #{endDate}  and `status`='settled'"
            + " and userId not in(select DISTINCT userId from takeout_order "
            + " where createAt >= #{endDate}  and `status`='settled')" + " group by shopId,`type`")
    List<StatDailyUser> statSlientCnt(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("select a.auserId,count(DISTINCT b.id) as cnt,"
            + "    count(if(b.createAt >= #{startDate} and b.createAt < #{endDate}, 1 ,null)) as newCnt,"
            + "    count(if(b.createAt < #{startDate}, 1 ,null)) as oldCnt"
            + "    from takeout_order a, `user` b where a.userId = b.id AND "
            + "    a.createAt >= #{startDate} and a.createAt < #{endDate} group by a.auserId")
    List<StatDailyUser> statDailyAllPlfUser(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("select shopId,`type` as shopType,count(1) as dupCnt from ("
            + "select shopId,`type` ,userId,count(userId) from takeout_order where "
            + "    createAt >= #{startDate} and createAt < #{endDate} and `status`='settled'"
            + "    group by shopId,`type`,userId having count(1)>=2 " + ") a group by shopId,`type`")
    List<StatDailyUser> statDupUser(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("select auserId,count(DISTINCT userId) as actCnt from takeout_order where "
            + "    createAt >= #{startDate} and `status`='settled' group by auserId ")
    List<StatDailyUser> statAllPlfActCnt(@Param("startDate") Date startDate);

    @Select("select auserId ,count(DISTINCT userId) as slientCnt from takeout_order where "
            + "    createAt >= #{startDate} and createAt <#{endDate}  and `status`='settled'" + " and userId not in( "
            + "    select DISTINCT a.userId from takeout_order a,`user` b where a.createAt >= #{endDate} "
            + "    and a.`status`='settled' and a.auserId= b.auserId)" + " group by auserId")
    List<StatDailyUser> statAllPlfSlientCnt(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    @Select("select auserId,count(1) as dupCnt from ("
            + "select auserId ,userId,count(userId) from takeout_order where "
            + "    createAt >= #{startDate} and createAt < #{endDate} and `status`='settled'"
            + "    group by auserId,userId having count(1)>=2 ) a group by auserId")
    List<StatDailyUser> statAllPlfDupUser(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

}
